#inc/models.py
# 标准库
from datetime import datetime, timedelta
import json , re


# 第三方库
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash
from flask_login import UserMixin

from inc.config import Config

db = SQLAlchemy()


jira_project_reviewers = db.Table(
    'jira_project_reviewers',
    db.Column('reviewer_id', db.Integer, db.ForeignKey('admin_member.id'), primary_key=True),  # 改为关联 admin_member.id
    db.Column('project_id', db.Integer, db.ForeignKey('jira_project.id'), primary_key=True)
)

#######################################后台管理#######################################

#数据库配置
class DatabaseConfig(db.Model):
    __tablename__ = 'admin_database_config'

    id = db.Column(db.Integer, primary_key=True)
    db_type = db.Column(db.String(20), nullable=False, comment="数据库类型")
    host = db.Column(db.String(100), comment="主机地址")
    port = db.Column(db.Integer, comment="端口")
    username = db.Column(db.String(50), comment="用户名")
    password = db.Column(db.String(100), comment="密码")
    database = db.Column(db.String(50), comment="数据库名")
    is_active = db.Column(db.Boolean, default=False, comment="是否激活")

#系统配置
class SystemConfig(db.Model):
    __tablename__ = 'admin_system_config'

    id = db.Column(db.Integer, primary_key=True)

    # 网站基本信息
    site_name = db.Column(db.String(100), default='项目管理系统')
    site_title = db.Column(db.String(200), default='项目管理系统 - 高效协作平台')
    site_keywords = db.Column(db.String(255), default='项目,管理,协作,企业微信')
    site_description = db.Column(db.Text)
    company_name = db.Column(db.String(100), default='某某科技有限公司')
    company_address = db.Column(db.String(200))
    contact_phone = db.Column(db.String(20))
    contact_email = db.Column(db.String(100))
    footer_info = db.Column(db.String(200), default='© 2023 某某科技有限公司 版权所有')
    icp_number = db.Column(db.String(50))

    # 新增日志相关配置
    operation_log_enabled = db.Column(db.Boolean, default=True, comment="是否启用操作日志")
    operation_log_retention_days = db.Column(db.Integer, default=365, comment="操作日志保留天数")
    backup_enabled = db.Column(db.Boolean, default=True, comment="是否启用备份功能")
    backup_schedule = db.Column(db.String(20), default="0 0 * * 0", comment="备份计划(cron表达式)")
    backup_retention_count = db.Column(db.Integer, default=30, comment="备份保留数量")



    # 时区配置
    timezone = db.Column(db.String(50), default='Asia/Shanghai', comment="系统时区")

    # 新增方法
    def should_log_operation(self):
        """检查是否应该记录操作日志"""
        return self.operation_log_enabled

    def get_backup_schedule(self):
        """获取备份计划"""
        return self.backup_schedule if self.backup_enabled else None

    @classmethod
    def get_config(cls):
        """获取系统配置单例"""
        config = cls.query.first()
        if not config:
            config = cls()
            db.session.add(config)
            db.session.commit()
        return config

    def update_config(self, **kwargs):
        """更新配置"""
        for key, value in kwargs.items():
            if hasattr(self, key):
                setattr(self, key, value)
        db.session.commit()


class AdminDepartment(db.Model):
    __tablename__ = 'admin_department'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False, comment="部门名称")
    parent_id = db.Column(db.Integer, db.ForeignKey('admin_department.id'), comment="父部门ID")
    order_num = db.Column(db.Integer, default=0, comment="排序号")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")
    update_time = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    # 添加企微部门ID用于关联
    wecom_id = db.Column(db.Integer, unique=True, comment="企业微信部门ID")
    # 添加审批人ID列表
    approver_ids = db.Column(db.String(255), default="", comment="部门审批人ID列表（逗号分隔）")


    # 树形结构关系
    children = db.relationship(
        "AdminDepartment",
        backref=db.backref("parent", remote_side=[id]),
        lazy="dynamic"
    )

    def get_path(self):
        """获取部门的完整路径"""
        path = []
        current = self
        while current:
            path.insert(0, current.name)
            current = current.parent
        return ' / '.join(path) if path else self.name

    def get_approvers(self):
        """获取部门审批人列表"""
        if not hasattr(self, 'approver_ids') or not self.approver_ids:
            return []
        return [int(id_str) for id_str in self.approver_ids.split(",")]

# 员工表
class AdminMember(db.Model):
    __tablename__ = 'admin_member'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False, comment="员工姓名")
    mobile = db.Column(db.String(20), comment="手机号")
    email = db.Column(db.String(100), comment="邮箱")
    position = db.Column(db.String(50), comment="职位")

    department_id = db.Column(db.Integer, db.ForeignKey('admin_department.id'), comment="所属部门ID")
    status = db.Column(db.Boolean, default=True, comment="状态（0-禁用 1-启用）")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")

    wecom_userid = db.Column(db.String(64), unique=True, comment="企业微信用户ID")

    password_hash = db.Column(db.String(128), comment="密码哈希值")
    reset_token = db.Column(db.String(128), comment="密码重置令牌")
    token_expires = db.Column(db.DateTime, comment="令牌过期时间")

    department = db.relationship("AdminDepartment", backref="members")

    # 新增审批人池字段
    approver_ids = db.Column(db.String(255), default="", comment="部门审批人ID列表（逗号分隔）")

    jira_reviews = db.relationship('JiraReview', back_populates='reviewer', foreign_keys='JiraReview.reviewer_id')

    # 新增反向引用：关联 JiraProject 的评审人
    reviewed_projects = db.relationship(
        'JiraProject',
        secondary='jira_project_reviewers',
        back_populates='reviewers'
    )


    # 新增方法
    def get_approvers(self):
        """获取部门审批人列表"""
        if not self.approver_ids:
            return []
        return [int(id_str) for id_str in self.approver_ids.split(",")]

    def set_approvers(self, member_ids):
        """设置部门审批人"""
        self.approver_ids = ",".join(str(id) for id in member_ids)

    def add_approver(self, member_id):
        """添加单个审批人"""
        approvers = set(self.get_approvers())
        approvers.add(member_id)
        self.set_approvers(approvers)

    def remove_approver(self, member_id):
        """移除单个审批人"""
        approvers = set(self.get_approvers())
        approvers.discard(member_id)
        self.set_approvers(approvers)

    def has_permission(self, permission_name):
        """检查用户是否有指定权限"""
        # 管理员默认拥有所有权限
        if self.position == 'admin':
            return True

        # 特定权限检查
        if permission_name in ['REVIEW_PROJECT', 'VIEW_PROJECTS']:
            return True  # 临时允许所有活跃成员查看项目

        return False

    def set_password(self, password):
        """设置密码哈希"""
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        """检查密码是否匹配"""
        if self.password_hash is None:
            return False
        return check_password_hash(self.password_hash, password)








# 后台管理员表
class AdminUser(db.Model, UserMixin):
    __tablename__ = 'admin_user'

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False, comment="登录账号")
    password_hash = db.Column(db.String(128), nullable=False, comment="密码哈希")
    real_name = db.Column(db.String(50), comment="真实姓名")
    last_login = db.Column(db.DateTime, comment="最后登录时间")
    avatar = db.Column(db.String(200), comment="头像URL")
    is_super = db.Column(db.Boolean, default=False, comment="超级管理员")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")
    login_attempts = db.Column(db.Integer, default=0, comment="登录尝试次数")
    password_changed_at = db.Column(db.DateTime, default=datetime.now, comment="密码最后修改时间")
    member_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="关联员工ID")

    # 关系
    member = db.relationship("AdminMember", backref=db.backref("admin_account", uselist=False))
    # 密码处理方法保持不变
    @property
    def password(self):
        raise AttributeError('password is not a readable attribute')

    @password.setter
    def password(self, password):
        self.password_hash = generate_password_hash(password)

    def verify_password(self, password):
        return check_password_hash(self.password_hash, password)

    @property
    def is_admin(self):
        """检查用户是否是管理员"""
        return self.is_super

    is_system = db.Column(db.Boolean, default=False, comment="是否为系统权限")

    # 新增字段：前端路由信息
    frontend_route = db.Column(db.String(200), comment="前端路由路径")
    icon = db.Column(db.String(50), comment="前端菜单图标")
    order_num = db.Column(db.Integer, default=0, comment="排序号")
    is_menu = db.Column(db.Boolean, default=False, comment="是否作为菜单显示")






#操作日志
class OperationLog(db.Model):
    __tablename__ = 'admin_operation_log'

    id = db.Column(db.Integer, primary_key=True)
    admin_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'))
    action = db.Column(db.String(100))
    resource = db.Column(db.String(100))
    timestamp = db.Column(db.DateTime, default=datetime.now)
    details = db.Column(db.Text)
    success = db.Column(db.Boolean, default=True)
    ip_address = db.Column(db.String(45), comment="操作IP地址")

    # 关系
    admin = db.relationship("AdminUser", backref="operation_logs")

    @classmethod
    def create(cls, admin_id, action, resource, details, success, ip_address):
        log = cls(
            admin_id=admin_id,
            action=action,
            resource=resource,
            details=details,
            success=success,
            ip_address=ip_address
        )
        db.session.add(log)
        db.session.commit()
        return log


#日志
class BackupLog(db.Model):
    __tablename__ = 'admin_backup_log'

    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(200))
    backup_time = db.Column(db.DateTime, default=datetime.now)
    file_size = db.Column(db.Integer)
    operator_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'))
    backup_type = db.Column(db.String(20), comment="备份类型")
    status = db.Column(db.String(10), nullable=False)  # success, failure

    # 关系
    operator = db.relationship('AdminUser', backref=db.backref('backup_logs', lazy='dynamic'))

    def __repr__(self):
        return f'<BackupLog {self.filename} {self.status}>'


    # 删除或注释掉原有的 create 方法，因为我们将在备份管理器中直接创建对象
    # @classmethod
    # def create(cls, **kwargs):
    #     """创建备份日志记录"""
    #     backup_log = cls(**kwargs)
    #     db.session.add(backup_log)
    #     db.session.commit()
    #     return backup_log




##############################企业微信模块########################################
class WechatConfig(db.Model):
    __tablename__ = 'wx_wechat_config'

    id = db.Column(db.Integer, primary_key=True)
    corp_id = db.Column(db.String(100), nullable=False)
    corp_secret = db.Column(db.String(100), nullable=False)
    agent_id = db.Column(db.String(100), nullable=True)
    access_token = db.Column(db.String(512), nullable=True)  # 增加长度
    token_expires = db.Column(db.DateTime, nullable=True)
    auto_sync = db.Column(db.Boolean, default=True)
    sync_interval = db.Column(db.Integer, default=3)  # 默认3小时
    last_sync_time = db.Column(db.DateTime)
    # 添加登录相关配置
    login_redirect_uri = db.Column(db.String(200), comment="登录回调地址")
    login_enabled = db.Column(db.Boolean, default=False, comment="是否启用企业微信登录")


    def is_token_valid(self):
        if not self.access_token or not self.token_expires:
            return False
        # 官方建议提前5分钟刷新
        return datetime.now() < self.token_expires - timedelta(minutes=5)


class SyncLog(db.Model):
    __tablename__ = 'wx_sync_log'

    id = db.Column(db.Integer, primary_key=True)
    sync_time = db.Column(db.DateTime, default=datetime.now)
    create_time = db.Column(db.DateTime, default=datetime.now)
    status = db.Column(db.String(20))
    message = db.Column(db.Text)
    departments_added = db.Column(db.Integer, default=0)
    departments_updated = db.Column(db.Integer, default=0)
    users_added = db.Column(db.Integer, default=0)
    users_updated = db.Column(db.Integer, default=0)


class Department(db.Model):
    __tablename__ = 'wx_department'

    id = db.Column(db.Integer, primary_key=True)
    wecom_id = db.Column(db.Integer, unique=True)  # 企业微信部门ID
    name = db.Column(db.String(100))
    parent_id = db.Column(db.Integer, db.ForeignKey('wx_department.id', ondelete='SET NULL'))
    order = db.Column(db.Integer)

    # 关系
    users = db.relationship('User', back_populates='department', lazy='dynamic')

    children = db.relationship('Department',
                               backref=db.backref('parent', remote_side='Department.id'),
                               foreign_keys=[parent_id])

    def user_count(self):
        return self.users.count()

    project_approvers = db.Column(db.String(255))  # 存储审批人ID列表






class User(db.Model):
    __tablename__ = 'wx_user'

    ACTIVE = 'active'
    INACTIVE = 'inactive'

    id = db.Column(db.Integer, primary_key=True)

    wecom_userid = db.Column(db.String(64), unique=True, index=True, comment="企业微信用户唯一ID")
    name = db.Column(db.String(64), comment="姓名")
    mobile = db.Column(db.String(32), comment="手机号")
    email = db.Column(db.String(128), comment="邮箱")
    position = db.Column(db.String(128), comment="职位")
    avatar = db.Column(db.String(255), comment="头像URL")
    status = db.Column(db.String(20), default=ACTIVE, comment="状态：active/inactive")

    department_id = db.Column(db.Integer, db.ForeignKey('wx_department.id', ondelete='SET NULL'))  # 修正字段名
    tags = db.Column(db.String(512))  # 增加长度

    # 关系
    department = db.relationship('Department', back_populates='users', lazy=True)

    # 部门快捷查询
    @property
    def department_name(self):
        return self.department.name if self.department else None

    @property
    def status_text(self):
        status_map = {1: '激活', 2: '禁用', 3: '已退出', 4: '未激活'}
        return status_map.get(self.status, '未知')


class Group(db.Model):
    __tablename__ = 'wx_group'

    # 状态常量
    ACTIVE = 1
    DISMISSING = 2  # 新增：解散中状态
    DISMISSED = 0

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), nullable=False)  # 群名称
    chat_id = db.Column(db.String(255), unique=True, nullable=False)  # 企业微信返回的群ID
    owner_id = db.Column(db.Integer, db.ForeignKey('wx_user.id'), nullable=False)  # 群主ID
    created_at = db.Column(db.DateTime, default=datetime.now)
    announcement = db.Column(db.Text, nullable=True)  # 群公告缓存
    status = db.Column(db.Integer, default=ACTIVE)  # 1-活跃, 0-已解散
    assigned_tags = db.Column(db.String(512))  # 增加标签存储长度
    dismiss_requested_at = db.Column(db.DateTime)

    # 关系
    owner = db.relationship('User', foreign_keys=[owner_id])
    members = db.relationship('GroupUser', backref='group_ref', lazy='dynamic', cascade='all, delete-orphan')

    def is_active(self):
        return self.status == self.ACTIVE

    # 新增便捷属性来获取群主的wecom_userid
    @property
    def owner_wecom_userid(self):
        return self.owner.wecom_userid if self.owner else None

    def get_matching_users(self):
        """获取符合群组标签要求的用户"""
        if not self.assigned_tags:
            return []

        tags = [t.strip() for t in self.assigned_tags.split(',')]
        query = User.query
        for tag in tags:
            query = query.filter(User.tags.contains(tag))
        return query.all()

    def get_non_member_users(self):
        """获取符合标签但尚未加入群组的用户"""
        matching_users = self.get_matching_users()
        current_member_ids = {gu.user_id for gu in self.members}
        return [u for u in matching_users if u.id not in current_member_ids]


class GroupUser(db.Model):
    __tablename__ = 'wx_group_user'

    # 状态常量
    ACTIVE = 1
    REMOVED = 0

    id = db.Column(db.Integer, primary_key=True)
    group_id = db.Column(db.Integer, db.ForeignKey('wx_group.id', ondelete='CASCADE'), nullable=False)  # 修正nullable
    user_id = db.Column(db.Integer, db.ForeignKey('wx_user.id'), nullable=False)
    joined_at = db.Column(db.DateTime, default=datetime.utcnow)
    assigned_tags = db.Column(db.String(512))  # 增加长度
    status = db.Column(db.SmallInteger, default=ACTIVE)

    # 关系
    user = db.relationship('User', foreign_keys=[user_id])


########################################jira项目管理#######################################
class JiraCategory(db.Model):
    __tablename__ = 'jira_category'

    """项目类别"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.now)

    projects = db.relationship('JiraProject', back_populates='category', lazy=True)


class JiraLevel(db.Model):
    __tablename__ = 'jira_level'

    """项目级别"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    priority = db.Column(db.Integer, default=0)  # 优先级数字，越大优先级越高
    color = db.Column(db.String(20), default='#007bff')  # 用于UI显示的颜色

    projects = db.relationship('JiraProject', back_populates='level', lazy=True)


class JiraProject(db.Model):
    __tablename__ = 'jira_project'

    DRAFT = 'draft'  # 草稿
    PENDING = 'pending_review'  # 待审核
    APPROVED = 'approved'  # 已批准
    REJECTED = 'rejected'  # 已拒绝
    PENDING_START = 'pending_start'  # 待开始
    IN_PROGRESS = 'in_progress'  # 进行中
    COMPLETED = 'completed'  # 已完成
    CANCELLED = 'cancelled'  # 已取消
    CLOSING = 'closing'  # 结项审核中
    CLOSED = 'closed'    # 已结项
    CLOSURE_REJECTED = 'closure_rejected'  # 结项被拒

    # 主键
    id = db.Column(db.Integer, primary_key=True)

    # 核心业务标识
    name = db.Column(db.String(200), nullable=False, comment="项目名称")
    description = db.Column(db.Text, comment="项目描述")

    # 分类与级别
    category_id = db.Column(db.Integer, db.ForeignKey('jira_category.id'), comment="项目类别ID")
    level_id = db.Column(db.Integer, db.ForeignKey('jira_level.id'), comment="项目级别ID")

    # 负责人与审批人
    owner_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False, comment="项目负责人（本地用户ID）")
    first_approver_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="第一审批人（本地用户ID）")  # 新增
    submitter_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False, comment="项目提交人（本地用户ID）")

    # 状态与群组
    status = db.Column(db.String(20), default='draft',
                       comment="项目状态：draft/pending_review/approved/rejected/in_progress/completed/closed等")
    group_id = db.Column(db.Integer, db.ForeignKey('wx_group.id'), comment="关联企业微信群ID")

    # 时间与进度
    started_at = db.Column(db.DateTime, comment="实际开始时间")
    end_date = db.Column(db.DateTime, comment="计划结束时间")
    progress = db.Column(db.Integer, default=0, comment="任务进度百分比（0-100）")

    # 审核相关
    submitted_at = db.Column(db.DateTime, comment="提交审核时间")
    reviewed_at = db.Column(db.DateTime, comment="审核时间")
    reviewer_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="审核人（后台管理员ID）")
    review_notes = db.Column(db.Text, comment="审核意见")

    # 结项相关
    closure_submitted_at = db.Column(db.DateTime, comment="结项提交时间")
    closure_reviewed_at = db.Column(db.DateTime, comment="结项审批时间")
    closure_notes = db.Column(db.Text, comment="结项审批意见")
    closure_score = db.Column(db.Float, comment="结项评分（0-100）")

    # 拒绝相关
    reject_reason = db.Column(db.Text, comment="打回原因")
    reject_count = db.Column(db.Integer, default=0, comment="被打回次数")

    # 审批流程
    approval_flow_id = db.Column(db.Integer, db.ForeignKey('jira_approval_flow.id'), comment="关联审批流程ID")
    current_approval_step = db.Column(db.Integer, default=0, comment="当前审批步骤（0=未开始）")
    approval_status = db.Column(db.String(20), default='pending', comment="审批状态：pending/approved/rejected")

    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.now, comment="创建时间")
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")

    # 关联关系
    category = db.relationship('JiraCategory', back_populates='projects', lazy=True)
    level = db.relationship('JiraLevel', back_populates='projects', lazy=True)
    owner = db.relationship('AdminMember', foreign_keys=[owner_id], backref='owned_projects')
    first_approver = db.relationship('AdminMember',foreign_keys=[first_approver_id], backref='first_approved_projects')  # 新增
    submitter = db.relationship('AdminMember', foreign_keys=[submitter_id], backref='submitted_projects')

    group = db.relationship('Group', backref='projects')
    approval_flow = db.relationship('JiraApprovalFlow', backref='projects', cascade='save-update, merge')

    # JiraProject 模型缺少对JiraStage和JiraMember的反向引用
    # 应该添加：
    stages = db.relationship('JiraStage', backref='project', lazy=True, cascade='all, delete-orphan')
    members = db.relationship('JiraMember', backref='project', lazy=True, cascade='all, delete-orphan')



    # 评审人关联 AdminMember（通过中间表）
    reviewers = db.relationship(
        'AdminMember',  # 关联 AdminMember
        secondary='jira_project_reviewers',  # 中间表
        back_populates='reviewed_projects'  # 需在 AdminMember 中新增反向引用
    )

    # 新增方法
    def update_progress(self):
        """自动计算并更新项目进度"""
        total_tasks = 0
        completed_tasks = 0

        for stage in self.stages:
            for task in stage.tasks:
                total_tasks += 1
                if task.status == task.COMPLETED:
                    completed_tasks += 1

        if total_tasks > 0:
            self.progress = int((completed_tasks / total_tasks) * 100)
        else:
            self.progress = 0

        return self.progress

    def can_close(self):
        """检查项目是否可以提交结项"""
        if self.status != self.COMPLETED:
            return False, "项目尚未完成"

        # 检查所有任务是否已完成
        for stage in self.stages:
            for task in stage.tasks:
                if task.status != task.COMPLETED:
                    return False, "存在未完成的任务"

        # 检查所有成员是否已评分
        for member in self.members:
            if member.score is None:
                return False, "存在未评分的成员"

        return True, ""

    def submit_closure(self):
        """提交结项申请"""
        can_close, reason = self.can_close()
        if not can_close:
            return False, reason

        self.status = self.CLOSING
        self.closure_submitted_at = datetime.now()
        return True, ""

    def approve_closure(self, reviewer_id, notes, score):
        """通过结项申请"""
        self.status = self.CLOSED
        self.closure_reviewed_at = datetime.now()
        self.closure_notes = notes
        self.closure_score = score
        self.reviewer_id = reviewer_id
        return True

    def reject_closure(self, reviewer_id, notes):
        """拒绝结项申请"""
        self.status = self.CLOSURE_REJECTED
        self.closure_reviewed_at = datetime.now()
        self.closure_notes = notes
        self.reviewer_id = reviewer_id
        return True


    # 新增方法
    def get_current_approval_step(self):
        """获取当前审批步骤对象"""
        if self.approval_flow and self.current_approval_step > 0:
            steps = sorted(self.approval_flow.steps, key=lambda s: s.order)
            if self.current_approval_step <= len(steps):
                return steps[self.current_approval_step - 1]
        return None

    def get_next_approval_step(self):
        """获取下一步审批步骤对象"""
        if self.approval_flow and self.current_approval_step > 0:
            steps = sorted(self.approval_flow.steps, key=lambda s: s.order)
            if self.current_approval_step < len(steps):
                return steps[self.current_approval_step]
        return None

    def get_approval_history(self):
        """获取审批历史记录"""
        return sorted(
            [r for r in self.reviews if r.step_order <= self.current_approval_step],
            key=lambda r: r.step_order
        )

    # 新增方法
    def start_task(self):
        """开始任务"""
        if self.status != self.DRAFT:
            return False
        self.status = self.IN_PROGRESS
        self.started_at = datetime.now()
        return True

    def complete_task(self):
        """完成任务"""
        if self.status != self.IN_PROGRESS:
            return False
        self.status = self.COMPLETED
        self.completed_at = datetime.now()
        return True

    def reject_task(self, reason):
        """打回任务"""
        if self.status != self.COMPLETED:
            return False
        self.status = self.REJECTED
        self.reject_reason = reason
        self.reject_count += 1
        return True

    def reset_task(self):
        """重置任务（被打回后重新开始）"""
        if self.status != self.REJECTED:
            return False
        self.status = self.TODO
        self.started_at = None
        self.progress = 0
        return True

    @property
    def is_startable(self):
        """任务是否可开始"""
        return self.status == self.TODO

    @property
    def is_rejectable(self):
        """任务是否可打回"""
        return self.status == self.COMPLETED

    @property
    def is_resettable(self):
        """任务是否可重置"""
        return self.status == self.REJECTED

    @property
    def status_label(self):
        """获取状态的可读标签"""
        status_map = {
            self.DRAFT: '草稿',
            self.PENDING: '待审核',
            self.APPROVED: '已批准',
            self.REJECTED: '已拒绝',
            self.PENDING_START: '待开始',
            self.IN_PROGRESS: '进行中',
            self.COMPLETED: '已完成',
            self.CANCELLED: '已取消',
            self.CLOSING: '结项审核中',
            self.CLOSED: '已结项',
            self.CLOSURE_REJECTED: '结项被拒'
        }
        return status_map.get(self.status, '未知状态')


class JiraMember(db.Model):
    """项目成员信息"""
    __tablename__ = 'jira_member'

    # 主键
    id = db.Column(db.Integer, primary_key=True)

    # 关联项目与用户
    project_id = db.Column(db.Integer, db.ForeignKey('jira_project.id'), nullable=False, comment="所属项目ID")
    member_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False, comment="项目成员（用户ID）")

    # 时间与评分
    joined_at = db.Column(db.DateTime, default=datetime.utcnow, comment="加入项目时间")
    score = db.Column(db.Float, default=0, comment="最终评分（0-100）")
    score_notes = db.Column(db.Text, comment="评分备注")

    # 关联关系
    member = db.relationship('AdminMember', backref='jira_memberships')


class JiraStage(db.Model):
    __tablename__ = 'jira_stage'

    """项目阶段表"""
    # 阶段状态
    NOT_STARTED = 'not_started'
    IN_PROGRESS = 'in_progress'
    COMPLETED = 'completed'
    BLOCKED = 'blocked'

    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('jira_project.id'), nullable=False)
    name = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text)
    order = db.Column(db.Integer, default=0)  # 阶段顺序
    status = db.Column(db.String(20), default=NOT_STARTED)
    start_date = db.Column(db.Date)
    end_date = db.Column(db.Date)
    completed_at = db.Column(db.DateTime)

    # 关系
    tasks = db.relationship('JiraTask', backref='stage', lazy=True, cascade='all, delete-orphan')

    # 禁用删除行数确认
    __mapper_args__ = {
        'confirm_deleted_rows': False
    }

class JiraTask(db.Model):
    __tablename__ = 'jira_task'

    # 任务状态常量
    TODO = 'todo'
    PENDING_ACCEPTANCE = 'pending_acceptance'  # 新增：待接受状态
    IN_PROGRESS = 'in_progress'
    COMPLETED = 'completed'
    BLOCKED = 'blocked'

    id = db.Column(db.Integer, primary_key=True)
    stage_id = db.Column(db.Integer, db.ForeignKey('jira_stage.id'), nullable=False)
    name = db.Column(db.String(200), nullable=False)
    rating = db.Column(db.Integer)  # 任务评分(1-5星)
    feedback = db.Column(db.Text)  # 任务评价反馈
    description = db.Column(db.Text)
    assignee_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=True)
    status = db.Column(db.String(20), default=TODO)
    priority = db.Column(db.Integer, default=0)  # 优先级
    due_date = db.Column(db.Date)
    progress = db.Column(db.Integer, default=0)  # 进度百分比(0-100)
    completed_at = db.Column(db.DateTime)

    # 关系
    assignee = db.relationship('AdminMember', backref='jira_assigned_tasks')

    @property
    def priority_label(self):
        """获取优先级的可读标签"""
        priority_map = {
            0: '无级别',
            1: '优先级低',
            2: '优先级中',
            3: '优先级高'
        }
        return priority_map.get(self.priority, '未知')

    @property
    def status_label(self):
        """获取状态的可读标签"""
        status_map = {
            'todo': '待办',
            'pending_acceptance': '待接受',
            'in_progress': '进行中',
            'completed': '已完成',
            'blocked': '已阻塞'
        }
        return status_map.get(self.status, '未知状态')



class JiraReviewNode(db.Model):
    __tablename__ = 'jira_review_node'

    """审核节点定义"""
    id = db.Column(db.Integer, primary_key=True)
    description = db.Column(db.Text)
    order = db.Column(db.Integer, default=0)  # 节点顺序

class JiraReview(db.Model):
    __tablename__ = 'jira_review'

    """项目审核记录表"""
    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('jira_project.id'), nullable=False)
    reviewer_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False)
    node_id = db.Column(db.Integer, db.ForeignKey('jira_review_node.id'), nullable=False)
    status = db.Column(db.String(20), nullable=False, default='pending')  # 审核结果: approved, rejected, pending
    notes = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    reviewed_at = db.Column(db.DateTime, comment="审批时间")  # 新增字段
    review_notes = db.Column(db.Text, comment="审批意见")  # 新增字段
    is_final = db.Column(db.Boolean, default=False, comment="是否为最终审批")
    is_closure_review = db.Column(db.Boolean, default=False, comment="是否为结项审核")
    submitter_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False, comment="提交人")
    submitter = db.relationship('AdminMember', foreign_keys=[submitter_id])

    # 关系
    reviewer = db.relationship('AdminMember', back_populates='jira_reviews', foreign_keys=[reviewer_id], overlaps="reviewers,jira_reviews")

    node = db.relationship('JiraReviewNode', backref='reviews')
    # 修改关系
    step_id = db.Column(db.Integer, db.ForeignKey('jira_approval_step.id'), comment="关联审批步骤")
    step = db.relationship('JiraApprovalStep', backref='reviews')

    project = db.relationship('JiraProject', backref='reviews', foreign_keys=[project_id])

    @property
    def type_label(self):
        """获取审批类型的可读标签"""
        return "项目审批"  # 可以根据实际业务逻辑调整

    @property
    def status_label(self):
        """获取状态的可读标签"""
        status_map = {
            'pending': '待审批',
            'approved': '已通过',
            'rejected': '已拒绝'
        }
        return status_map.get(self.status, '未知状态')


class JiraComment(db.Model):
    __tablename__ = 'jira_comment'

    """任务评论表"""
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.Integer, db.ForeignKey('jira_task.id'), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False)
    content = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    task = db.relationship('JiraTask', backref='comments')
    author = db.relationship('AdminMember', backref='jira_comments')


class JiraApprovalFlow(db.Model):
    """审批流程模板"""
    __tablename__ = 'jira_approval_flow'

    # 主键
    id = db.Column(db.Integer, primary_key=True)

    # 核心信息
    name = db.Column(db.String(100), nullable=False, comment="流程名称")
    description = db.Column(db.Text, comment="流程描述")

    # 关联部门（可选）
    department_id = db.Column(db.Integer, db.ForeignKey('admin_department.id'), comment="关联部门ID")
    is_default = db.Column(db.Boolean, default=False, comment="是否为默认流程")

    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment="创建时间")

    # 关联关系
    department = db.relationship('AdminDepartment', backref='approval_flows', lazy=True)
    steps = db.relationship('JiraApprovalStep', backref='flow', cascade='all, delete-orphan',
                            order_by='JiraApprovalStep.order')



class JiraApprovalStep(db.Model):
    """审批步骤定义"""
    __tablename__ = 'jira_approval_step'

    # 主键
    id = db.Column(db.Integer, primary_key=True)

    # 流程关联
    flow_id = db.Column(db.Integer, db.ForeignKey('jira_approval_flow.id'), nullable=False, comment="所属流程ID")
    order = db.Column(db.Integer, nullable=False, comment="步骤顺序（从1开始）")

    # 审批人配置
    user_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="审批人（后台管理员ID）")
    require_comment = db.Column(db.Boolean, default=False, comment="是否强制填写审批意见")

    # 时间戳
    created_at = db.Column(db.DateTime, default=datetime.utcnow, comment="创建时间")

    # 关联关系
    user = db.relationship('AdminMember', backref='approval_steps', lazy=True)

class ProjectAttachment(db.Model):
    """项目附件表"""
    __tablename__ = 'jira_project_attachment'

    id = db.Column(db.Integer, primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('jira_project.id'), nullable=False)
    uploader_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filepath = db.Column(db.String(512), nullable=False)
    filesize = db.Column(db.Integer, nullable=False)
    filetype = db.Column(db.String(50))
    description = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    project = db.relationship('JiraProject', backref='attachments')
    uploader = db.relationship('AdminMember', backref='uploaded_attachments')


class TaskAttachment(db.Model):
    __tablename__ = 'jira_task_attachments'

    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.Integer, db.ForeignKey('jira_task.id'), nullable=False)
    filename = db.Column(db.String(255), nullable=False)
    filepath = db.Column(db.String(512), nullable=False)
    description = db.Column(db.Text)
    uploader_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    task = db.relationship('JiraTask', backref='attachments')
    uploader = db.relationship('AdminMember', backref='task_uploaded_attachments')

###############################中控模块########################################



class ZktimeDevice(db.Model):
    __tablename__ = 'zktime_devices'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    ip_address = db.Column(db.String(15), nullable=False)
    port = db.Column(db.Integer, default=4370)
    protocol = db.Column(db.String(10), default='UDP')
    password = db.Column(db.String(100))
    description = db.Column(db.Text)
    status = db.Column(db.SmallInteger, default=1, comment="0-离线 1-在线 2-故障")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    last_heartbeat = db.Column(db.DateTime, comment="最后心跳时间")
    heartbeat_interval = db.Column(db.Integer, default=60, comment="心跳检测间隔(秒)")
    offline_threshold = db.Column(db.Integer, default=180, comment="离线判定阈值(秒)")
    retry_count = db.Column(db.Integer, default=0, comment="连接重试次数")
    last_status = db.Column(db.SmallInteger, default=1, comment="最后检测状态")

    # 关系
    access_logs = db.relationship('ZktimeAccessLog', backref='device', lazy=True, cascade='all, delete-orphan')

    def save(self):
        db.session.add(self)
        db.session.commit()

    def delete(self):
        db.session.delete(self)
        db.session.commit()

    @staticmethod
    def get_all():
        return ZktimeDevice.query.all()

    @staticmethod
    def get_by_id(device_id):
        return ZktimeDevice.query.get(device_id)

    # 新增方法
    def update_heartbeat(self, status=True):
        """更新心跳时间和状态"""
        self.last_heartbeat = datetime.now()
        self.last_status = 1 if status else 0
        if status:
            self.retry_count = 0  # 重置重试计数
            self.status = 1  # 更新设备状态为在线
        else:
            self.retry_count += 1
            # 如果重试次数超过阈值，标记设备为离线
            if self.retry_count >= 3:  # 可配置的重试次数
                self.status = 0


    def is_online(self):
        if not self.last_heartbeat: return False
        time_diff = (datetime.now() - self.last_heartbeat).total_seconds()
        return time_diff <= self.offline_threshold

    @staticmethod
    def get_offline_devices():
        """获取所有离线设备"""
        threshold = datetime.now() - timedelta(seconds=180)  # 默认3分钟阈值
        return ZktimeDevice.query.filter(
            (ZktimeDevice.last_heartbeat < threshold) |
            (ZktimeDevice.last_status == 0)
        ).all()

    @staticmethod
    def get_online_devices():
        """获取所有在线设备"""
        threshold = datetime.now() - timedelta(seconds=180)  # 默认3分钟阈值
        return ZktimeDevice.query.filter(
            ZktimeDevice.last_heartbeat >= threshold,
            ZktimeDevice.last_status == 1
        ).all()



class ZktimeAccessLog(db.Model):
    __tablename__ = 'zktime_access_logs'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), nullable=True)
    device_id = db.Column(db.Integer, db.ForeignKey('zktime_devices.id'), nullable=False)
    action = db.Column(db.String(50), default='open')
    result = db.Column(db.String(20), default='success')
    ip_address = db.Column(db.String(45))
    created_at = db.Column(db.DateTime, default=datetime.now)
    details = db.Column(db.Text)

    # 关系
    user = db.relationship("AdminMember", backref="access_logs")


    def save(self):
        db.session.add(self)
        db.session.commit()

    @staticmethod
    def get_all_with_details():
        """获取所有日志记录及关联的设备和管理员信息"""
        results = db.session.query(
            ZktimeAccessLog,
            ZktimeDevice.name.label('device_name'),
            AdminMember.name.label('user_name')
        ).join(
            ZktimeDevice, ZktimeAccessLog.device_id == ZktimeDevice.id
        ).outerjoin(
            AdminMember, ZktimeAccessLog.user_id == AdminMember.id
        ).order_by(
            ZktimeAccessLog.created_at.desc()
        ).all()

        # 将结果转换为字典列表，方便模板使用
        logs = []
        for log, device_name, user_name in results:
            logs.append({
                'log': log,
                'device_name': device_name,
                'user_name': user_name
            })
        return logs

    @staticmethod
    def get_user_logs(user_id, limit=50):
        from sqlalchemy.orm import aliased
        User = aliased(AdminMember)  # 替换为实际的用户模型

        return db.session.query(
            ZktimeAccessLog,
            ZktimeDevice.name.label('device_name')
        ).join(
            ZktimeDevice, ZktimeAccessLog.device_id == ZktimeDevice.id
        ).filter(
            ZktimeAccessLog.user_id == user_id
        ).order_by(
            ZktimeAccessLog.created_at.desc()
        ).limit(limit).all()


class AttendanceRecord(db.Model):
    __tablename__ = 'zktime_attendance_records'

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String(50), nullable=False)  # 设备中的用户ID
    device_id = db.Column(db.Integer, db.ForeignKey('zktime_devices.id'), nullable=False)
    timestamp = db.Column(db.DateTime, nullable=False)
    status = db.Column(db.Integer, comment="出入状态")
    verify_type = db.Column(db.Integer, comment="验证方式")
    workcode = db.Column(db.Integer, comment="工作代码")
    ip_address = db.Column(db.String(45))
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    device = db.relationship('ZktimeDevice', backref='attendance_records')

    def save(self):
        db.session.add(self)
        db.session.commit()


#########################################资产管理###########################################

# 中间表定义
asset_supplier_link = db.Table(
    'asset_supplier_link',
    db.Column('asset_id', db.Integer, db.ForeignKey('asset.id'), primary_key=True),
    db.Column('supplier_id', db.Integer, db.ForeignKey('asset_supplier.id'), primary_key=True)
)

asset_custom_field_link = db.Table(
    'asset_custom_field_link',
    db.Column('asset_id', db.Integer, db.ForeignKey('asset.id'), primary_key=True),
    db.Column('field_id', db.Integer, db.ForeignKey('asset_custom_field.id'), primary_key=True),
    db.Column('field_value', db.Text, nullable=True)
)

class AssetCategory(db.Model):
    """资产类别"""
    __tablename__ = 'asset_category'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, unique=True, comment="类别名称")
    description = db.Column(db.Text, comment="类别描述")
    parent_id = db.Column(db.Integer, db.ForeignKey('asset_category.id'), comment="父类别ID")
    order_num = db.Column(db.Integer, default=0, comment="排序号")
    is_active = db.Column(db.Boolean, default=True, comment="是否启用")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    # 树形结构关系
    children = db.relationship(
        "AssetCategory",
        backref=db.backref("parent", remote_side=[id]),
        lazy="dynamic",
        cascade='all, delete-orphan'
    )
    assets = db.relationship('Asset', back_populates='category', lazy=True)
    custom_fields = db.relationship('AssetCustomField', back_populates='category', cascade='all, delete-orphan')

    def get_path(self):
        """获取类别的完整路径"""
        path = []
        current = self
        while current:
            path.insert(0, current.name)
            current = current.parent
        return ' / '.join(path) if path else self.name

    def get_all_children_ids(self):
        """获取所有子类别ID（包括子类别的子类别）"""
        ids = [self.id]
        for child in self.children:
            ids.extend(child.get_all_children_ids())
        return ids

    def __repr__(self):
        return f'<AssetCategory {self.name}>'


class AssetSupplier(db.Model):
    """供应商信息"""
    __tablename__ = 'asset_supplier'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200), nullable=False, unique=True, comment="供应商名称")
    contact_person = db.Column(db.String(100), comment="联系人")
    contact_email = db.Column(db.String(100), comment="联系邮箱")
    contact_phone = db.Column(db.String(50), comment="联系电话")
    address = db.Column(db.Text, comment="地址")
    website = db.Column(db.String(200), comment="网站")
    is_active = db.Column(db.Boolean, default=True, comment="是否启用")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)



class AssetCustomField(db.Model):
    """自定义字段配置"""
    __tablename__ = 'asset_custom_field'

    # 字段类型常量
    TYPE_TEXT = 'text'
    TYPE_NUMBER = 'number'
    TYPE_DATE = 'date'
    TYPE_SELECT = 'select'
    TYPE_BOOLEAN = 'boolean'
    TYPE_TEXTAREA = 'textarea'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, comment="字段名称")
    field_type = db.Column(db.String(50), nullable=False, comment="字段类型")
    options = db.Column(db.Text, comment="选项值(JSON格式，用于select类型)")
    is_required = db.Column(db.Boolean, default=False, comment="是否必填")
    category_id = db.Column(db.Integer, db.ForeignKey('asset_category.id'), comment="关联类别ID")
    order_num = db.Column(db.Integer, default=0, comment="排序号")
    is_active = db.Column(db.Boolean, default=True, comment="是否启用")
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    category = db.relationship('AssetCategory', back_populates='custom_fields')
    attributes = db.relationship('AssetAttribute', back_populates='field', cascade='all, delete-orphan')

    def get_options_list(self):
        """获取选项列表"""
        if self.field_type == self.TYPE_SELECT and self.options:
            try:
                return json.loads(self.options)
            except:
                return []
        return []

    def validate_value(self, value):
        """验证字段值"""
        if self.is_required and not value:
            return False, "此字段为必填项"

        if self.field_type == self.TYPE_NUMBER and value:
            try:
                float(value)
            except ValueError:
                return False, "请输入有效的数字"

        if self.field_type == self.TYPE_SELECT and value:
            options = self.get_options_list()
            if value not in options:
                return False, "请选择有效的选项"

        return True, "验证通过"

    def __repr__(self):
        return f'<AssetCustomField {self.name} ({self.field_type})>'


class Asset(db.Model):
    """资产信息主表"""
    __tablename__ = 'asset'

    # 资产状态常量
    DRAFT = 'draft'  # 草稿
    PENDING = 'pending'  # 待确认
    IN_USE = 'in_use'  # 使用中
    AVAILABLE = 'available'  # 可用
    MAINTENANCE = 'maintenance'  # 维护中
    RETIRED = 'retired'  # 已报废
    LOST = 'lost'  # 丢失
    RESERVED = 'reserved'  # 预留

    # 确认状态常量
    CONFIRMATION_PENDING = 'pending'
    CONFIRMATION_CONFIRMED = 'confirmed'
    CONFIRMATION_REJECTED = 'rejected'

    id = db.Column(db.Integer, primary_key=True)

    # 基础信息
    name = db.Column(db.String(200), nullable=False, comment="资产名称")
    asset_number = db.Column(db.String(100), unique=True, nullable=False, comment="资产编号")
    category_id = db.Column(db.Integer, db.ForeignKey('asset_category.id'), nullable=False, comment="资产类别ID")
    model = db.Column(db.String(200), comment="型号")
    brand = db.Column(db.String(100), comment="品牌")
    serial_number = db.Column(db.String(100), unique=True, comment="序列号")

    # 财务信息
    purchase_price = db.Column(db.Numeric(10, 2), comment="购买价格")
    purchase_date = db.Column(db.Date, comment="购买日期")
    warranty_months = db.Column(db.Integer, comment="保修月数")
    warranty_expiry = db.Column(db.Date, comment="保修截止日期")
    depreciation_rate = db.Column(db.Numeric(5, 2), comment="折旧率(%)")
    current_value = db.Column(db.Numeric(10, 2), comment="当前价值")

    # 使用信息
    assigned_to_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="分配给用户ID")
    assigned_date = db.Column(db.Date, comment="分配日期")
    location = db.Column(db.String(200), comment="存放位置")
    status = db.Column(db.String(50), default=PENDING, comment="资产状态")

    # 技术规格
    specifications = db.Column(db.Text, comment="技术规格(JSON格式)")

    # 硬件信息（从Agent提取）
    cpu_info = db.Column(db.Text, comment="CPU信息")
    memory_info = db.Column(db.Text, comment="内存信息")
    disk_info = db.Column(db.Text, comment="磁盘信息")
    network_info = db.Column(db.Text, comment="IP地址")
    mac_info = db.Column(db.String(100), comment="MAC地址")
    os_info = db.Column(db.Text, comment="操作系统信息")
    user_info = db.Column(db.Text, comment="用户信息")

    # 描述信息
    description = db.Column(db.Text, comment="资产描述")
    notes = db.Column(db.Text, comment="备注")

    # Agent相关字段
    host_identifier = db.Column(db.String(128), unique=True, nullable=True, comment="硬件唯一标识")
    report_time = db.Column(db.DateTime, comment="Agent最后上报时间")
    agent_id = db.Column(db.String(128), db.ForeignKey('asset_agents.agent_id'), comment="关联Agent ID")
    agent_version = db.Column(db.String(50), comment="Agent版本")

    # 发现和管理状态
    auto_discovered = db.Column(db.Boolean, default=False, comment="是否自动发现")
    discovery_method = db.Column(db.String(50), default='manual', comment="发现方式: agent/manual")
    confirmation_status = db.Column(db.String(20), default=CONFIRMATION_PENDING, comment="确认状态")



    # 系统字段
    created_by_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'), comment="创建人")
    confirmed_by_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'), comment="确认人")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)
    confirmed_at = db.Column(db.DateTime, comment="确认时间")

    # 关系
    category = db.relationship('AssetCategory', back_populates='assets')
    assigned_to = db.relationship('AdminMember', backref='assigned_assets', foreign_keys=[assigned_to_id])
    created_by = db.relationship('AdminUser', foreign_keys=[created_by_id], backref='created_assets')
    confirmed_by = db.relationship('AdminUser', foreign_keys=[confirmed_by_id], backref='confirmed_assets')
    maintenance_logs = db.relationship('AssetMaintenanceLog', back_populates='asset', cascade='all, delete-orphan')
    attachments = db.relationship('AssetAttachment', back_populates='asset', cascade='all, delete-orphan')
    attributes = db.relationship('AssetAttribute', back_populates='asset', cascade='all, delete-orphan')
    suppliers = db.relationship('AssetSupplier', secondary='asset_supplier_link', backref='assets')
    operation_logs = db.relationship('AssetOperationLog', back_populates='asset', cascade='all, delete-orphan')

    @property
    def formatted_cpu(self):
        """格式化CPU信息 - 简洁版本"""
        if self.cpu_info:
            try:
                if isinstance(self.cpu_info, dict):
                    cpu_data = self.cpu_info
                else:
                    cpu_data = json.loads(self.cpu_info)

                physical = cpu_data.get('cores', 0)
                logical = cpu_data.get('logical_cores', 0)
                brand_raw = cpu_data.get('brand_raw', '未知处理器')

                # 简单清理品牌字符串
                cleaned_brand = brand_raw.replace('(R)', '').replace('(TM)', '').strip()

                return f"{cleaned_brand} {physical}C/{logical}T"

            except Exception as e:
                print(f"CPU信息解析错误: {e}")
                return "N/A"
        return "N/A"

    @property
    def formatted_memory(self):
        """格式化内存信息"""
        if self.memory_info:
            try:
                mem_data = json.loads(self.memory_info)
                total_gb = mem_data.get('total', 0) / (1024 ** 3)
                return f"{total_gb:.1f} GB"
            except:
                return "N/A"
        return "N/A"

    @property
    def formatted_disk(self):
        """格式化磁盘信息"""
        if self.disk_info:
            try:
                disk_data = json.loads(self.disk_info)
                if isinstance(disk_data, list) and disk_data:
                    # 计算总容量
                    total_gb = sum(disk.get('total', 0) for disk in disk_data) / (1024 ** 3)
                    return f"{total_gb:.1f} GB"
                else:
                    return "N/A"
            except Exception as e:
                print(f"磁盘信息解析错误: {e}")
                return "N/A"
        return "N/A"

    @property
    def formatted_os(self):
        """格式化操作系统信息"""
        if self.os_info:
            try:
                os_data = json.loads(self.os_info)
                # 直接提取需要的字段
                os_description = os_data.get('os_description', '')
                architecture = os_data.get('architecture', '')

                if os_description and architecture:
                    return f"{os_description} {architecture}"
                elif os_description:
                    return os_description
                elif architecture:
                    return f"Unknown OS {architecture}"
                else:
                    return "N/A"

            except Exception as e:
                print(f"系统信息解析错误: {e}")
                return "N/A"
        return "N/A"

    @property
    def formatted_network(self):
        """格式化网络信息"""
        if self.network_info:
            try:
                net_data = json.loads(self.network_info)
                ip_address = net_data.get('ip_address', '未知')
                return ip_address
            except Exception as e:
                print(f"网络信息解析错误: {e}")
                return "N/A"
        return "N/A"

    def _format_mac_with_colon(self, mac_str):
        """统一格式化MAC地址为冒号分隔"""
        if not mac_str:
            return "N/A"
        # 移除所有非字母数字字符
        clean_mac = re.sub(r'[^a-fA-F0-9]', '', mac_str)
        # 每两个字符加冒号
        return ':'.join([clean_mac[i:i+2] for i in range(0, 12, 2)]).upper()

    @property
    def formatted_mac(self):
        """格式化MAC地址 - 精确版本"""
        if not self.mac_info:
            return "N/A"

        # 精确优先的适配器名称
        exact_priority = [
            "WLAN",  # 无线网络
            "Wi-Fi",
            "以太网",  # 有线网络
            "Ethernet"
        ]

        # 严格排除的虚拟设备
        strict_virtual_keywords = [
            "VirtualBox", "VMware", "蓝牙", "Bluetooth",
            "Loopback", "Teredo", "本地连接*", "Host-Only"
        ]

        if isinstance(self.mac_info, dict):
            mac_dict = self.mac_info
        elif isinstance(self.mac_info, str):
            try:
                mac_dict = json.loads(self.mac_info)
            except:
                return self._format_mac_with_colon(self.mac_info)

        # 精确匹配优先
        for exact_name in exact_priority:
            for key, value in mac_dict.items():
                if exact_name.lower() == key.lower():  # 精确匹配
                    return self._format_mac_with_colon(value)

        # 包含匹配
        for exact_name in exact_priority:
            for key, value in mac_dict.items():
                if exact_name.lower() in key.lower():
                    # 严格检查虚拟设备
                    if not any(virt in key for virt in strict_virtual_keywords):
                        return self._format_mac_with_colon(value)

        return "N/A"

    @property
    def formatted_user(self):
        """格式化用户信息 - 改进版本"""
        if not self.user_info:
            return "N/A"

        try:
            # 如果user_info是字符串，尝试JSON解析
            if isinstance(self.user_info, str):
                user_data = json.loads(self.user_info)
            # 如果已经是列表或字典，直接使用
            else:
                user_data = self.user_info

            # 处理列表格式（如您提供的数据）
            if isinstance(user_data, list) and len(user_data) > 0:
                user_obj = user_data[0]  # 取第一个用户
                username = user_obj.get('name', '未知用户')
                return username

            # 处理字典格式
            elif isinstance(user_data, dict):
                username = user_data.get('username') or user_data.get('name', '未知用户')
                full_name = user_data.get('full_name', '')

                if full_name:
                    return f"{full_name} ({username})"
                return username

        except json.JSONDecodeError:
            # 如果不是JSON，直接返回字符串
            return str(self.user_info)
        except Exception as e:
            print(f"用户信息解析错误: {e}")
            return "N/A"

        return "N/A"

    @property
    def warranty_status(self):
        """获取保修状态"""
        if not self.warranty_expiry:
            return 'unknown'
        today = datetime.now().date()
        if today > self.warranty_expiry:
            return 'expired'
        elif (self.warranty_expiry - today).days <= 30:
            return 'expiring_soon'
        else:
            return 'valid'

    @property
    def status_label(self):
        """获取状态的可读标签"""
        status_map = {
            self.DRAFT: '草稿',
            self.PENDING: '待确认',
            self.IN_USE: '使用中',
            self.AVAILABLE: '可用',
            self.MAINTENANCE: '维护中',
            self.RETIRED: '已报废',
            self.LOST: '丢失',
            self.RESERVED: '预留'
        }
        return status_map.get(self.status, '未知状态')

    @property
    def confirmation_status_label(self):
        """获取确认状态的可读标签"""
        status_map = {
            self.CONFIRMATION_PENDING: '待确认',
            self.CONFIRMATION_CONFIRMED: '已确认',
            self.CONFIRMATION_REJECTED: '已拒绝'
        }
        return status_map.get(self.confirmation_status, '未知')

    def calculate_depreciation(self):
        """计算资产折旧（年折旧率）"""
        if not self.purchase_price or not self.purchase_date:
            return None

        # 计算持有年数（精确到小数点后2位）
        days_owned = (datetime.now().date() - self.purchase_date).days
        if days_owned <= 0:
            return self.purchase_price

        years_owned = round(days_owned / 365.25, 2)  # 考虑闰年，使用365.25天/年

        # 折旧计算：购买价格 × (年折旧率/100) × 年数
        depreciation_amount = float(self.purchase_price) * (float(self.depreciation_rate or 0) / 100) * years_owned
        current_value = float(self.purchase_price) - depreciation_amount
        return max(current_value, 0)

    def can_edit(self):
        """检查资产是否可以编辑"""
        return self.confirmation_status != self.CONFIRMATION_CONFIRMED

    def confirm_asset(self, user_id, notes=None):
        """确认资产"""
        self.confirmation_status = self.CONFIRMATION_CONFIRMED
        self.confirmed_by_id = user_id
        self.confirmed_at = datetime.now()
        if notes:
            self.notes = notes

    def reject_asset(self, user_id, reason):
        """拒绝资产"""
        self.confirmation_status = self.CONFIRMATION_REJECTED
        self.confirmed_by_id = user_id
        self.confirmed_at = datetime.now()
        self.notes = f"拒绝原因: {reason}"

    def get_custom_fields_dict(self):
        """获取自定义字段的字典形式"""
        return {attr.field.name: attr.attribute_value for attr in self.attributes}

    def update_from_agent_data(self, agent_data):
        """从Agent数据更新资产信息"""
        if not self.auto_discovered:
            return  # 只更新自动发现的资产

        # 更新硬件信息（如果字段为空）
        if not self.cpu_info and 'cpu' in agent_data:
            self.cpu_info = json.dumps(agent_data['cpu'])
        if not self.memory_info and 'memory' in agent_data:
            self.memory_info = json.dumps(agent_data['memory'])
        if not self.disk_info and 'disks' in agent_data:
            self.disk_info = json.dumps(agent_data['disks'])
        if not self.os_info and 'system_info' in agent_data:
            self.os_info = json.dumps(agent_data['system_info'])
        if not self.network_info and 'network_info' in agent_data:
            self.network_info = json.dumps(agent_data['network_info'])

        self.last_agent_report = datetime.now()

    def __repr__(self):
        return f'<Asset {self.asset_number} - {self.name}>'


class AssetMaintenanceLog(db.Model):
    """资产维护记录"""
    __tablename__ = 'asset_maintenance_log'

    # 维护类型常量
    MAINTENANCE = 'maintenance'  # 日常维护
    REPAIR = 'repair'  # 维修
    UPGRADE = 'upgrade'  # 升级
    INSPECTION = 'inspection'  # 检查
    CALIBRATION = 'calibration'  # 校准

    # 维护状态常量
    STATUS_PLANNED = 'planned'  # 计划中
    STATUS_IN_PROGRESS = 'in_progress'  # 进行中
    STATUS_COMPLETED = 'completed'  # 已完成
    STATUS_CANCELLED = 'cancelled'  # 已取消

    id = db.Column(db.Integer, primary_key=True)
    asset_id = db.Column(db.Integer, db.ForeignKey('asset.id'), nullable=False, comment="关联资产ID")
    maintenance_type = db.Column(db.String(50), default=MAINTENANCE, comment="维护类型")
    title = db.Column(db.String(200), nullable=False, comment="维护标题")
    description = db.Column(db.Text, comment="维护描述")
    performed_by_id = db.Column(db.Integer, db.ForeignKey('admin_member.id'), comment="维护执行人ID")
    maintenance_date = db.Column(db.DateTime, default=datetime.now, comment="维护日期")
    next_maintenance_date = db.Column(db.Date, comment="下次维护日期")
    cost = db.Column(db.Numeric(10, 2), default=0.0, comment="维护费用")
    duration_hours = db.Column(db.Numeric(5, 2), comment="维护时长(小时)")
    parts_replaced = db.Column(db.Text, comment="更换零件(JSON格式)")
    status = db.Column(db.String(50), default=STATUS_COMPLETED, comment="维护状态")
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    asset = db.relationship('Asset', back_populates='maintenance_logs')
    performed_by = db.relationship('AdminMember', backref='performed_maintenances')

    @property
    def maintenance_type_label(self):
        """获取维护类型标签"""
        type_map = {
            self.MAINTENANCE: '日常维护',
            self.REPAIR: '维修',
            self.UPGRADE: '升级',
            self.INSPECTION: '检查',
            self.CALIBRATION: '校准'
        }
        return type_map.get(self.maintenance_type, '未知类型')

    @property
    def status_label(self):
        """获取状态标签"""
        status_map = {
            self.STATUS_PLANNED: '计划中',
            self.STATUS_IN_PROGRESS: '进行中',
            self.STATUS_COMPLETED: '已完成',
            self.STATUS_CANCELLED: '已取消'
        }
        return status_map.get(self.status, '未知状态')

    def get_parts_list(self):
        """获取更换零件列表"""
        if self.parts_replaced:
            try:
                return json.loads(self.parts_replaced)
            except:
                return []
        return []

    def __repr__(self):
        return f'<AssetMaintenanceLog {self.title} for Asset {self.asset_id}>'


class AssetAttachment(db.Model):
    """资产附件"""
    __tablename__ = 'asset_attachment'

    id = db.Column(db.Integer, primary_key=True)
    asset_id = db.Column(db.Integer, db.ForeignKey('asset.id'), nullable=False, comment="关联资产ID")
    filename = db.Column(db.String(255), nullable=False, comment="文件名")
    filepath = db.Column(db.String(512), nullable=False, comment="文件路径")
    file_size = db.Column(db.Integer, comment="文件大小(字节)")
    file_type = db.Column(db.String(100), comment="文件类型")
    description = db.Column(db.Text, comment="文件描述")
    uploaded_by_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'), comment="上传人ID")
    uploaded_at = db.Column(db.DateTime, default=datetime.now, comment="上传时间")

    # 关系
    asset = db.relationship('Asset', back_populates='attachments')
    uploaded_by = db.relationship('AdminUser', backref='uploaded_asset_attachments')

    @property
    def file_size_formatted(self):
        """获取格式化的文件大小"""
        if not self.file_size:
            return "0 B"

        size = self.file_size
        for unit in ['B', 'KB', 'MB', 'GB']:
            if size < 1024.0:
                return f"{size:.1f} {unit}"
            size /= 1024.0
        return f"{size:.1f} TB"

    def __repr__(self):
        return f'<AssetAttachment {self.filename} for Asset {self.asset_id}>'


class AssetAttribute(db.Model):
    """资产自定义属性值"""
    __tablename__ = 'asset_attribute'

    id = db.Column(db.Integer, primary_key=True)
    asset_id = db.Column(db.Integer, db.ForeignKey('asset.id'), nullable=False)
    field_id = db.Column(db.Integer, db.ForeignKey('asset_custom_field.id'), nullable=False)
    attribute_value = db.Column(db.Text, nullable=False, comment="属性值")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    # 关系
    asset = db.relationship('Asset', back_populates='attributes')
    field = db.relationship('AssetCustomField', back_populates='attributes')

    def __repr__(self):
        return f'<AssetAttribute {self.field_id} for Asset {self.asset_id}>'


class AssetAgentConfig(db.Model):
    """资产采集代理配置"""
    __tablename__ = 'asset_agent_config'

    # 代理状态常量
    STATUS_ONLINE = 'online'
    STATUS_OFFLINE = 'offline'
    STATUS_ERROR = 'error'
    STATUS_UNKNOWN = 'unknown'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False, unique=True, comment="配置名称")
    agent_ip = db.Column(db.String(45), nullable=False, comment="代理IP地址")
    agent_port = db.Column(db.Integer, default=8080, comment="代理端口")
    protocol = db.Column(db.String(10), default='http', comment="通信协议")
    auth_token = db.Column(db.String(255), comment="认证令牌")
    polling_interval = db.Column(db.Integer, default=300, comment="轮询间隔(秒)")
    last_polled_at = db.Column(db.DateTime, comment="最后轮询时间")
    last_status = db.Column(db.String(20), default=STATUS_UNKNOWN, comment="最后状态")
    is_active = db.Column(db.Boolean, default=True, comment="是否启用")

    # 新增字段
    agent_type = db.Column(db.String(50), default='standalone', comment="代理类型")
    version = db.Column(db.String(20), comment="代理版本")
    capabilities = db.Column(db.Text, comment="支持的功能(JSON)")
    description = db.Column(db.Text, comment="配置描述")

    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    def is_online(self):
        """检查代理是否在线"""
        if not self.last_polled_at:
            return False
        time_diff = (datetime.now() - self.last_polled_at).total_seconds()
        return time_diff <= self.polling_interval * 2  # 允许2倍间隔时间

    def get_capabilities_list(self):
        """获取支持的功能列表"""
        if self.capabilities:
            try:
                return json.loads(self.capabilities)
            except:
                return []
        return []

    def update_status(self, status, message=None):
        """更新代理状态"""
        self.last_status = status
        self.last_polled_at = datetime.now()

    def __repr__(self):
        return f'<AssetAgentConfig {self.name} ({self.agent_ip}:{self.agent_port})>'


class AssetOperationLog(db.Model):
    """资产操作日志"""
    __tablename__ = 'asset_operation_log'

    # 操作类型常量
    OP_CREATE = 'create'
    OP_UPDATE = 'update'
    OP_DELETE = 'delete'
    OP_CONFIRM = 'confirm'
    OP_REJECT = 'reject'
    OP_ASSIGN = 'assign'
    OP_TRANSFER = 'transfer'
    OP_MAINTENANCE = 'maintenance'
    OP_AGENT_REPORT = 'agent_report'

    id = db.Column(db.Integer, primary_key=True)
    asset_id = db.Column(db.Integer, db.ForeignKey('asset.id'), nullable=False)
    operator_id = db.Column(db.Integer, db.ForeignKey('admin_user.id'), nullable=False)
    operation_type = db.Column(db.String(50), nullable=False, comment="操作类型")
    operation_details = db.Column(db.Text, comment="操作详情")
    ip_address = db.Column(db.String(45), comment="操作IP")
    user_agent = db.Column(db.String(255), comment="用户代理")
    created_at = db.Column(db.DateTime, default=datetime.now)

    # 关系
    asset = db.relationship('Asset', back_populates='operation_logs')
    operator = db.relationship('AdminUser', backref='asset_operations')

    @property
    def operation_type_label(self):
        """获取操作类型标签"""
        type_map = {
            self.OP_CREATE: '创建资产',
            self.OP_UPDATE: '更新资产',
            self.OP_DELETE: '删除资产',
            self.OP_CONFIRM: '确认资产',
            self.OP_REJECT: '拒绝资产',
            self.OP_ASSIGN: '分配资产',
            self.OP_TRANSFER: '转移资产',
            self.OP_MAINTENANCE: '维护记录',
            self.OP_AGENT_REPORT: 'Agent上报'
        }
        return type_map.get(self.operation_type, '未知操作')

    def get_details_dict(self):
        """获取操作详情字典"""
        if self.operation_details:
            try:
                return json.loads(self.operation_details)
            except:
                return {}
        return {}

    def __repr__(self):
        return f'<AssetOperationLog {self.operation_type} for Asset {self.asset_id}>'


class AssetAgent(db.Model):
    """资产Agent信息"""
    __tablename__ = 'asset_agents'  # 表名保持不变

    agent_id = db.Column(db.String(128), primary_key=True, comment="Agent唯一标识")
    agent_name = db.Column(db.String(100), nullable=False, comment="Agent名称")
    host_identifier = db.Column(db.String(128), unique=True, nullable=False, comment="主机标识")
    last_seen = db.Column(db.DateTime, default=datetime.now, comment="最后活动时间")
    agent_version = db.Column(db.String(50), comment="Agent版本")
    capabilities = db.Column(db.Text, comment="支持的功能")
    status = db.Column(db.String(20), default='active', comment="状态")
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)

    # 关系
    assets = db.relationship('Asset', backref='agent', foreign_keys='Asset.agent_id')

    def is_online(self):
        """检查Agent是否在线"""
        if not self.last_seen:
            return False
        time_diff = (datetime.now() - self.last_seen).total_seconds()
        return time_diff <= 300  # 5分钟阈值

    def get_capabilities_list(self):
        """获取支持的功能列表"""
        if self.capabilities:
            try:
                return json.loads(self.capabilities)
            except:
                return []
        return []

    def __repr__(self):
        return f'<AssetAgent {self.agent_name} ({self.agent_id})>'